const mysql = require('mysql2/promise');
const fs = require('fs');
require('dotenv').config();

async function initDatabase() {
  console.log('🚀 初始化数据库...');
  
  try {
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      port: process.env.DB_PORT,
      user: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_DATABASE,
    });

    console.log('✅ 数据库连接成功！');

    // 读取SQL文件
    const sqlContent = fs.readFileSync('init-remote-database.sql', 'utf8');
    
    // 分割SQL语句
    const statements = sqlContent
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0 && !stmt.startsWith('--') && !stmt.startsWith('USE'));

    console.log(`📝 执行 ${statements.length} 条SQL语句...`);

    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i];
      if (statement.trim()) {
        try {
          await connection.execute(statement);
          console.log(`✅ 执行语句 ${i + 1}/${statements.length}`);
        } catch (error) {
          if (error.code === 'ER_TABLE_EXISTS_ERROR') {
            console.log(`⚠️  表已存在，跳过: ${statement.substring(0, 50)}...`);
          } else {
            console.error(`❌ 执行语句失败: ${error.message}`);
            console.error(`   语句: ${statement.substring(0, 100)}...`);
          }
        }
      }
    }

    // 验证表是否创建成功
    console.log('🔍 验证表创建结果...');
    const [tables] = await connection.execute('SHOW TABLES');
    console.log('📋 现有表:');
    tables.forEach(table => {
      console.log(`   - ${Object.values(table)[0]}`);
    });

    // 检查users表
    const [usersTable] = await connection.execute("SHOW TABLES LIKE 'users'");
    if (usersTable.length > 0) {
      console.log('✅ users表创建成功！');
      
      // 检查是否有数据
      const [userCount] = await connection.execute('SELECT COUNT(*) as count FROM users');
      console.log(`📊 用户数量: ${userCount[0].count}`);
    } else {
      console.log('❌ users表创建失败');
    }

    await connection.end();
    console.log('🎉 数据库初始化完成！');
    
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
    process.exit(1);
  }
}

initDatabase();
